package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;

import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 客户投诉查询
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/customerComplaintsController")
public class CustomerComplaintsController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(CustomerComplaintsController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 客户投诉 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "customerComplaintsList")
	public ModelAndView customerComplaintsList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/customerComplaintsList");

	}



	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{


		
		String sql = " SELECT QMClientServiceLog.FBillNo,isnull(t_SubMessage.FName,'') AS FBase1_DSPName,t_ICItem.FNumber AS FItemID_DSPName,t_ICItem.FName AS FItemID71959DSPName, ";
				sql += " t_ICItem.FModel AS FItemID71969_DSPName,t_Measureunit.FName AS FUnitID_DSPName,Convert(decimal(28,2),QMClientServiceLog.FQty) FQty,QMClientServiceLog.FBatchNo,t_Emp.FName AS FDealerID_DSPName,Convert(decimal(28,2),QMClientServiceLog.FAppealSum) FAppealSum, ";
				sql += " QMClientServiceLog.FAppealDT,QMAinfo2.FName AS FAppealStatusID_DSPName,QMAInfo3.FName AS FAppealWayID_DSPName,QMAInfo1.FName AS FApppealTypeID_DSPName,QMClientServiceLog.FAppealContent,QMClientServiceLog.FDealResult, ";
				sql += " isnull(t_Organization.FName,'') AS FBase_DSPName,QMClientServiceLog.FMemo,t_User.FName AS FBillerID_DSPName,QMClientServiceLog.FCreateDT,t_user2.FName AS FCheckerID_DSPName,QMClientServiceLog.FCheckDate ";
				sql += " FROM RemoteServer.AIS2009.dbo.ICClassGroupInfo  LEFT JOIN RemoteServer.AIS2009.dbo.QMClientServiceLog  ON ICClassGroupInfo.FID=QMClientServiceLog.FID ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem  ON QMClientServiceLog.FItemID=t_ICItem.FItemID AND t_ICItem.FItemID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit  ON QMClientServiceLog.FUnitID=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp  ON QMClientServiceLog.FDealerID=t_Emp.FItemID AND t_Emp.FItemID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_SubMessage  ON QMClientServiceLog.FBase1=t_SubMessage.FInterID AND t_SubMessage.FInterID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Organization  ON QMClientServiceLog.FBase=t_Organization.FItemID AND t_Organization.FItemID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAinfo QMAinfo2 ON QMClientServiceLog.FAppealStatusID=QMAinfo2.FID ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAInfo QMAInfo3 ON QMClientServiceLog.FAppealWayID=QMAInfo3.FID ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAInfo QMAInfo1 ON QMClientServiceLog.FApppealTypeID=QMAInfo1.FID ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User  ON QMClientServiceLog.FBillerID=t_User.FUserID AND t_User.FUserID<>0 ";
				sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User t_user2 ON QMClientServiceLog.FCheckerID=t_user2.FUserID AND t_user2.FUserID<>0 ";
                sql += " where 1=1 and isnull(QMClientServiceLog.FCheckerID,0)>0 ";
		
		
		
		
		
				String Fdate_begin = request.getParameter("FCreateDT_begin");
				String Fdate_end = request.getParameter("FCreateDT_end");
				String FBillNo = request.getParameter("FBillNo");
				String FSupplyName = request.getParameter("FBase.DSPName");
				String FItemID71959_DSPName = request.getParameter("FItemID71959DSPName");
				String FAppealContent = request.getParameter("FAppealContent");
		
				if (StringUtil.isNotEmpty(Fdate_begin))
				{
					sql += " and QMClientServiceLog.FCreateDT >= " + "'" + Fdate_begin + "'";
				}
				
				if (StringUtil.isNotEmpty(Fdate_end))
				{
					sql += " and QMClientServiceLog.FCreateDT <= " + "'" + Fdate_end + "'";
				}
				if (StringUtil.isNotEmpty(FBillNo))
				{
					sql += " and QMClientServiceLog.FBillNo = " + "'" + FBillNo + "'";
				}
				if (StringUtil.isNotEmpty(FSupplyName))
				{
					sql += " and t_Organization.FName like " + "'%" + FSupplyName + "%'";
				}
				if (StringUtil.isNotEmpty(FItemID71959_DSPName))
				{
					sql += " and t_ICItem.FName like " + "'%" + FItemID71959_DSPName + "%'";
				}
				if (StringUtil.isNotEmpty(FAppealContent))
				{
					sql += " and QMClientServiceLog.FAppealContent like " + "'%" + FAppealContent + "%'";
				}

				// 排序
				if(dataGrid.getSort() != null)
				{
					String orderField = dataGrid.getSort();
					if(dataGrid.getSort().equals(orderField)){
						sql += " order by " + orderField + dataGrid.getOrder();
					}
				}

		List<Map<String, Object>> allmaplist = null;
		List<Map<String, Object>> maplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());
			
			
		} catch (Exception ex)
		{

		} finally
		{

		}

		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		BigDecimal sum_qty = new BigDecimal("0.00");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("FAppealSum"));
		}
		dataGrid.setFooter("FBase_DSPName:合计," + "FAppealSum:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);

	}

   
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	     
		String sql = " SELECT QMClientServiceLog.FBillNo,isnull(t_SubMessage.FName,'') AS FBase1_DSPName,t_ICItem.FNumber AS FItemID_DSPName,t_ICItem.FName AS FItemID71959DSPName, ";
		sql += " t_ICItem.FModel AS FItemID71969_DSPName,t_Measureunit.FName AS FUnitID_DSPName,Convert(decimal(28,2),QMClientServiceLog.FQty) FQty,QMClientServiceLog.FBatchNo,t_Emp.FName AS FDealerID_DSPName,Convert(decimal(28,2),QMClientServiceLog.FAppealSum) FAppealSum, ";
		sql += " Convert(varchar(50),QMClientServiceLog.FAppealDT,23) FAppealDT ,QMAinfo2.FName AS FAppealStatusID_DSPName,QMAInfo3.FName AS FAppealWayID_DSPName,QMAInfo1.FName AS FApppealTypeID_DSPName,QMClientServiceLog.FAppealContent,QMClientServiceLog.FDealResult, ";
		sql += " isnull(t_Organization.FName,'') AS FBase_DSPName,QMClientServiceLog.FMemo,t_User.FName AS FBillerID_DSPName,Convert(varchar(50),QMClientServiceLog.FCreateDT,23) FCreateDT,t_user2.FName AS FCheckerID_DSPName,QMClientServiceLog.FCheckDate ";
		sql += " FROM RemoteServer.AIS2009.dbo.ICClassGroupInfo  LEFT JOIN RemoteServer.AIS2009.dbo.QMClientServiceLog  ON ICClassGroupInfo.FID=QMClientServiceLog.FID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem  ON QMClientServiceLog.FItemID=t_ICItem.FItemID AND t_ICItem.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Measureunit  ON QMClientServiceLog.FUnitID=t_Measureunit.FItemID AND t_Measureunit.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Emp  ON QMClientServiceLog.FDealerID=t_Emp.FItemID AND t_Emp.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_SubMessage  ON QMClientServiceLog.FBase1=t_SubMessage.FInterID AND t_SubMessage.FInterID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Organization  ON QMClientServiceLog.FBase=t_Organization.FItemID AND t_Organization.FItemID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAinfo QMAinfo2 ON QMClientServiceLog.FAppealStatusID=QMAinfo2.FID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAInfo QMAInfo3 ON QMClientServiceLog.FAppealWayID=QMAInfo3.FID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.QMAInfo QMAInfo1 ON QMClientServiceLog.FApppealTypeID=QMAInfo1.FID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User  ON QMClientServiceLog.FBillerID=t_User.FUserID AND t_User.FUserID<>0 ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_User t_user2 ON QMClientServiceLog.FCheckerID=t_user2.FUserID AND t_user2.FUserID<>0 ";
        sql += " where 1=1 and isnull(QMClientServiceLog.FCheckerID,0)>0 ";





		String Fdate_begin = request.getParameter("FCreateDT_begin");
		String Fdate_end = request.getParameter("FCreateDT_end");
		String FBillNo = request.getParameter("FBillNo");
		String FSupplyName = request.getParameter("FBase.DSPName");
		String FItemID71959_DSPName = request.getParameter("FItemID71959DSPName");
		String FAppealContent = request.getParameter("FAppealContent");

		if (StringUtil.isNotEmpty(Fdate_begin))
		{
			sql += " and QMClientServiceLog.FCreateDT >= " + "'" + Fdate_begin + "'";
		}
		
		if (StringUtil.isNotEmpty(Fdate_end))
		{
			sql += " and QMClientServiceLog.FCreateDT <= " + "'" + Fdate_end + "'";
		}
		if (StringUtil.isNotEmpty(FBillNo))
		{
			sql += " and QMClientServiceLog.FBillNo = " + "'" + FBillNo + "'";
		}
		if (StringUtil.isNotEmpty(FSupplyName))
		{
			sql += " and t_Organization.FName like " + "'%" + FSupplyName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemID71959_DSPName))
		{
			sql += " and t_ICItem.FName like " + "'%" + FItemID71959_DSPName + "%'";
		}
		if (StringUtil.isNotEmpty(FAppealContent))
		{
			sql += " and QMClientServiceLog.FAppealContent like " + "'%" + FAppealContent + "%'";
		}

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				sql += " order by " + orderField + dataGrid.getOrder();
			}
		}
		
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FCreateDT");
				arr[1] = (String) map.get("FBase_DSPName");
				arr[2] = (String) map.get("FItemID_DSPName");
				arr[3] = (String) map.get("FItemID71959DSPName");
				arr[4] = (String) map.get("FItemID71969_DSPName");
				arr[5] = String.valueOf((BigDecimal) map.get("FQty"));
				arr[6] = (String) map.get("FUnitID_DSPName");
				arr[7] = (String) map.get("FBatchNo");
				arr[8] = (String) map.get("FDealerID_DSPName");
				arr[9] = (String) map.get("FBase1_DSPName");
				arr[10] = String.valueOf((BigDecimal) map.get("FAppealSum"));
				arr[11] =  (String) map.get("FAppealDT");
				arr[12] = (String) map.get("FApppealTypeID_DSPName");  
				arr[13] = (String) map.get("FAppealContent");
				arr[14] = (String) map.get("FDealResult");
				arr[15] = (String) map.get("FBillNo");
				list.add(arr);
				 
			}
		
	
	
			String[] columnNames = new String[] { "制单日期", "客户名称", "产品代码", "物料名称","规格型号","宽度","单位","批号","责任人","责任工序","投诉数量","投诉日期","投诉类型","投诉内容","投诉处理结果","单据编号"};
	       String titleName = "客户投诉记录.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}

}
